//
//  RollDB.m
//  Roll Ball1.1.1
//
//  Created by Mac on 10-7-28.
//  Copyright 2010 __MyCompanyName__. All rights reserved.
//

#import "RollDB.h"


@implementation RollDB

-(void) checkAndInitDatabase{
	// Check if the SQL database has already been saved to the users phone, if not then copy it over
	BOOL success;
	
	// Create a FileManager object, we will use this to check the status
	// of the database and to copy it over if required
	NSFileManager *fileManager = [NSFileManager defaultManager];
	
	// Check if the database has already been created in the users filesystem
	success = [fileManager fileExistsAtPath:databasePath];
	
	// If the database already exists then return without doing anything
	if(success) return;
	
	// If not then proceed to copy the database from the application to the users filesystem
	
	// Get the path to the database in the application package
	NSString *databasePathFromApp = [[[NSBundle mainBundle] resourcePath] stringByAppendingPathComponent:databaseName];
	
	// Copy the database from the package to the users filesystem
	[fileManager copyItemAtPath:databasePathFromApp toPath:databasePath error:nil];
	
	[fileManager release];
}

-(void) initDB{
	// init databaseName
	databaseName = @"roll_db.sql";
	// Get the path to the documents directory and append the databaseName
	NSArray *documentPaths = NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES);
	NSString *documentsDir = [documentPaths objectAtIndex:0];
	databasePath = [documentsDir stringByAppendingPathComponent:databaseName];
	
	[self checkAndInitDatabase];
}

-(NSMutableArray *)	findAllRecord{
	// must init before use the method find***
	sqlite3 *database;
	
	NSMutableArray *records;
	
	records = [[NSMutableArray alloc] init];
	
	//Open Database
	if (sqlite3_open([databasePath UTF8String], &database) == SQLITE_OK) {
		const char *sqlStatement = "select * from top order by score desc";
		sqlite3_stmt *compiledStatement;
		if(sqlite3_prepare_v2(database, sqlStatement, -1, &compiledStatement, NULL) == SQLITE_OK) {
			// Loop through the results and add them to the feeds array
			while(sqlite3_step(compiledStatement) == SQLITE_ROW) {
				//Read data
				int uid = (int)sqlite3_column_text(compiledStatement, 1);
				NSString *uname = [NSString stringWithUTF8String:(char *)sqlite3_column_text(compiledStatement, 2)];
				int score = (int)sqlite3_column_text(compiledStatement, 3);
				NSString *playTime = [NSString stringWithUTF8String:(char *)sqlite3_column_text(compiledStatement, 4)];
				NSString *logTime = [NSString stringWithUTF8String:(char *)sqlite3_column_text(compiledStatement, 5)];
				
				//create TopRecord
				TopRecord *record = [[TopRecord alloc] initWithUid:uid Uname:uname Score:score PlayTime:playTime LogTime:logTime];
				[records addObject:record];
			}
		}
		// Release the compiled statement from memory
		sqlite3_finalize(compiledStatement);
	}
	sqlite3_close(database);
	
	return records;
}

-(void) insertRecord: (TopRecord *) record{
	// must init before use the method find***
	sqlite3 *database;
	if (sqlite3_open([databasePath UTF8String], &database) == SQLITE_OK){
		NSString *query = [NSString stringWithFormat:@"insert into top (uname,score,playtime,logtime) values ('%@',%i,'%@','%@')",record.uname,record.score,record.playtime,record.logtime];
		char* errmsg;
		if (sqlite3_exec(database, [query UTF8String], NULL, NULL, &errmsg)) {
			NSLog(@"error sql: %s",errmsg);
		}
	}
	sqlite3_close(database);
}

-(void) deleteRecordWithId: (int) rid{
	// must init before use the method find***
	sqlite3 *database;
	if (sqlite3_open([databasePath UTF8String], &database) == SQLITE_OK){
		NSString *query = [NSString stringWithFormat:@"delete from top where uid='%i'",rid];
		char* errmsg;
		if (sqlite3_exec(database, [query UTF8String], NULL, NULL, &errmsg)) {
			NSLog(@"error sql: %s",errmsg);
		}
	}
	sqlite3_close(database);
}

@end
